package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Entity.TrustDistricts;
import Entity.TrustRegions;


public class Dao_Trust {
	private Connection conn;
	private Statement sta;
	private ResultSet rs;

	public void getConnection() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String url = "jdbc:sqlserver://localhost:1130;"
				+ "databaseName=MockProject;";
		String user = "sa";
		String password = "12345678";
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void close() {
		if (rs != null) {
			try {
				rs.close();
				if (sta != null)
					sta.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public ArrayList<TrustRegions> getTrustRegions() {
		ArrayList<TrustRegions> list = new ArrayList<TrustRegions>();
		getConnection();
		try {
			sta = conn.createStatement();
			String sql = "select * from TrustRegions ";
			rs = sta.executeQuery(sql);
			String nameTR;
			String desTR;
			String naCou;
			boolean active;
			while (rs.next()) {
				nameTR = rs.getString("nameTR");
				naCou = rs.getString("naCou");
				desTR = rs.getString("desTR");
				active = rs.getBoolean("active");
				TrustRegions tr = new TrustRegions(nameTR, desTR, naCou, active);
				System.out.println(nameTR);
				list.add(tr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
			return list;
		}
	}
	
	public void setDsTrustRegion(String nameTR, String desTR, String naCou, boolean active) {
		try {
			getConnection();
			sta = conn.createStatement();
			String sql = "Insert Into TrustRegions(nameTR, desTR, naCou, active) values"
					+ "( " + "'" + nameTR + "'," + "'" + desTR + "'," + "'"
					+ naCou + "',";
			//if (active) {
				sql += "1 )";
			//} else {
			//	sql += "0 )";
			//}

			sta.executeUpdate(sql);
			sta.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
	}
	
	public void setUpdateTrustRegion(String nameTR, String desTR, String naCou, boolean active){
		try {
			getConnection();
			sta = conn.createStatement();
			String sql = "Update TrustRegions set nameTR='" + nameTR + "', desTR='" + desTR + "', naCou='" + naCou + "',active =";
			if (active) {
				sql += "1 ";
			} else {
				sql += "0 ";
			}
			sql += " WHERE nameTR='" + nameTR + "'";
			sta.executeUpdate(sql);
			sta.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
	}
	
	public ArrayList<TrustRegions> getDsTRActive(){
		ArrayList<TrustRegions> ds = new ArrayList<TrustRegions>();
		try {
			getConnection();
			sta = conn.createStatement();
			String sql="Select * from TrustRegions WHERE active = 1 ";
			rs= sta.executeQuery(sql);
			String nameTR;
			String desTR;
			String naCou;
			boolean active;
			while(rs.next()){
				nameTR=rs.getString("nameTR");
				desTR=rs.getString("desTR");
				naCou=rs.getString("naCou");
				active=rs.getBoolean("active");
				TrustRegions me = new TrustRegions(nameTR, desTR, naCou, active);
				ds.add(me);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
			return ds;
		}
		
	}
	
///////////////////////////////////////////////////////////
	
	public ArrayList<TrustDistricts> getTrustDistricts(String trustRegionName) {
		ArrayList<TrustDistricts> listDis = new ArrayList<TrustDistricts>();
		getConnection();
		try {
			sta = conn.createStatement();
			String sql = "select * from TrustDistricts WHERE nameTR='"
					+ trustRegionName + "'";
			rs = sta.executeQuery(sql);
			String nameTD;
			String desTD;
			String region;
			boolean active;
			String nameTR;
			while (rs.next()) {
				nameTD = rs.getString("nameTD");
				desTD = rs.getString("desTD");
				region = rs.getString("region");
				active = rs.getBoolean("active");
				nameTR = rs.getString("nameTR");
				TrustDistricts td = new TrustDistricts(nameTD, desTD, region, active, nameTR);
				listDis.add(td);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
			return listDis;
		}
	}
	
	public void setDsTrustDistrict(String nameTR,String nameTD,String desTD) {
		getConnection();
		try {
			String query = "INSERT INTO TrustDistricts(nameTD, desTD, nameTR, active) VALUES (?,?,?,1)";
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setString(1, nameTD);
			ps.setString(2, desTD);
			ps.setString(3, nameTR);
			ps.executeUpdate();
			System.out.print("sql="+query);
		} catch (SQLException e) {
			System.out.print(e);
		}
	}
	
	public ArrayList<TrustDistricts> getTrustDistricts() {
		ArrayList<TrustDistricts> listAllDis = new ArrayList<TrustDistricts>();
		getConnection();
		try {
			sta = conn.createStatement();
			String sql = "select * from TrustDistricts ";
			rs = sta.executeQuery(sql);
			String nameTD;
			String desTD;
			String region;
			boolean active;
			String nameTR;
			while (rs.next()) {
				nameTD = rs.getString("nameTD");
				desTD = rs.getString("desTD");
				region = rs.getString("region");
				active = rs.getBoolean("active");
				nameTR = rs.getString("nameTR");
				TrustDistricts tdd = new TrustDistricts(nameTD, desTD, region, active, nameTR);
				listAllDis.add(tdd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
			return listAllDis;
		}
	}
}
































